package Dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

import Bean.*;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

import cart.ShoppingCart;
import cart.ShoppingCartItem;

import exception.BooksNotFoundException;


public class OrderDao {
	ArrayList<BeanOrder>orders;
	private JdbcTemplate jdbcTemplate;
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	public void AddOrder(String userid,ShoppingCart cart) throws Exception {
    	Timestamp date =new Timestamp(System.currentTimeMillis());
		double price=0;
		for(ShoppingCartItem item:cart.getItems()){
			price+=item.getItem().getClass_price();
		}
		String sql="insert into `order`(order_time,order_price,user_id) VALUES(?,?,?)";
		this.jdbcTemplate.update(sql,new Object[] {date,price,userid});
		int orderid=this.jdbcTemplate.queryForObject("select max(order_id) from `order`", Integer.class);
		sql="insert into order_details(order_id,class_id,student_id,detail_price,isFormal) VALUES(?,?,?,?,?)";
		for(ShoppingCartItem item:cart.getItems()){
			this.jdbcTemplate.update(sql,new Object[] {orderid,item.getItem().getClass_id(),item.getStudent().getStudent_id(),item.getItem().getClass_price(),true});
		}
		for(ShoppingCartItem item:cart.getItems()){
			int num=this.jdbcTemplate.queryForObject("select num from class where class_id=?", Integer.class,item.getItem().getClass_id());
			sql="update class set num=? where class_id=?";
			this.jdbcTemplate.update(sql,new Object[] {num-1,item.getItem().getClass_id()});
			if(num==1){
				sql="update class set class_state=? where class_id=?";
				this.jdbcTemplate.update(sql,new Object[] {1,item.getItem().getClass_id()});
			}
		}
	}
	public void AddOrderShiXun(String userid,int class_id,int student_id,double class_price) throws Exception {
		String selectStatement = "select * from `order_details` where isFormal=false and class_id=? and student_id=?";
		BeanOrderDetail orderDetail=this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanOrderDetail>() {
			@Override
			public BeanOrderDetail extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				if(rs.next()) {
					BeanOrderDetail orderDetail1=new BeanOrderDetail();
					return orderDetail1;
				}
				return null;
			}
		},class_id,student_id);
		if(orderDetail!=null)throw new Exception("该学生已试训过该课程！");
		else{
			Timestamp date =new Timestamp(System.currentTimeMillis());
			String sql="insert into `order`(order_time,user_id) VALUES(?,?)";
			this.jdbcTemplate.update(sql,new Object[] {date,userid});
			int orderid=this.jdbcTemplate.queryForObject("select max(order_id) from `order`", Integer.class);
			sql="insert into order_details(order_id,class_id,student_id,detail_price,isFormal) VALUES(?,?,?,?,?)";
			this.jdbcTemplate.update(sql,new Object[] {orderid,class_id,student_id,class_price,false});
		}
	}
    public Collection<BeanOrder> getOrders(String userid) throws BooksNotFoundException{
        orders= new ArrayList<>();
        String selectStatement = "select order_id,order_time,order_price from `order` where user_id=?";
		this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanOrder>() {
			@Override
			public BeanOrder extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				while(rs.next()) {
					BeanOrder order=new BeanOrder();
					order.setOrder_id(rs.getInt(1));
					order.setOrder_time(rs.getDate(2));
					order.setUser_id(userid);
					order.setOrder_price(rs.getFloat(3));
					orders.add(order);
				}
				return null;
			}
		},userid);
		if(orders.size()>0) {
			Map<Integer, BeanOrder>mp=new HashMap<>();
			for(BeanOrder o:orders) {
				mp.put(o.getOrder_id(), o);
			}
			String orderids="";
			for(int i=0;i<orders.size();i++) {
				if(i!=0) orderids+=",";
				orderids+=orders.get(i).getOrder_id();
			}
			selectStatement="select order_id,class_id,student_id,detail_price from order_details where order_id in ("+orderids+") order by order_id desc";
			Set<Integer>classids=new HashSet<>();
			Set<Integer>stduentids=new HashSet<>();
			this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanOrderDetail>() {
				@Override
				public BeanOrderDetail extractData(ResultSet rs) throws SQLException, DataAccessException {
					// TODO Auto-generated method stub
					while(rs.next()) {
						BeanOrderDetail orderDetails=new BeanOrderDetail();
						orderDetails.setOrder_id(rs.getInt(1));
						orderDetails.setClass_id(rs.getInt(2));
						orderDetails.setStudent_id(rs.getInt(3));
						orderDetails.setDetail_price(rs.getFloat(4));
						classids.add(rs.getInt(2));
						stduentids.add(rs.getInt(3));

						BeanOrder order=mp.get(orderDetails.getOrder_id());
						if(order!=null) {
							if(order.getOrderDetails()==null) {
								order.setOrderDetails(new ArrayList<>());
							}
							order.getOrderDetails().add(orderDetails);
						}
					}
					return null;
				}
			});

			String strclassids="";
			for(int classid:classids) {
				if(strclassids=="") strclassids=classid+"";
				else strclassids+=","+classid;
			}

			selectStatement="select class_id,class_name,class_price,class_state,class_url from class where class_id in ("+strclassids+") order by class_id desc";

			Map<Integer, BeanClass>classMap=new HashMap<>();
			this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanClass>() {
				@Override
				public BeanClass extractData(ResultSet rs) throws SQLException, DataAccessException {
					// TODO Auto-generated method stub
					while(rs.next()) {
						BeanClass aClass=new BeanClass();
						aClass.setClass_id(rs.getInt(1));
						aClass.setClass_name(rs.getString(2));
						aClass.setClass_price(rs.getFloat(3));
						aClass.setClass_state(rs.getBoolean(4));
						aClass.setClass_url(rs.getString(5));
						classMap.put(aClass.getClass_id(), aClass);
					}
					return null;
				}
			});
			String strstudentids="";
			for(int studentid:stduentids) {
				if(strstudentids=="") strstudentids=studentid+"";
				else strstudentids+=","+studentid;
			}
			selectStatement="select student_id,student_name,student_age from student where student_id in ("+strstudentids+") order by student_id desc";

			Map<Integer, BeanStudent>studentMap=new HashMap<>();
			this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanStudent>() {
				@Override
				public BeanStudent extractData(ResultSet rs) throws SQLException, DataAccessException {
					// TODO Auto-generated method stub
					while(rs.next()) {
						BeanStudent student=new BeanStudent();
						student.setStudent_id(rs.getInt(1));
						student.setStudent_name(rs.getString(2));
						student.setStudent_age(rs.getInt(3));
						studentMap.put(student.getStudent_id(), student);
					}
					return null;
				}
			});
			for(BeanOrder orderList:orders) {
				for(BeanOrderDetail d:orderList.getOrderDetails()) {
					d.setStudent(studentMap.get(d.getStudent_id()));
					d.setaClass(classMap.get(d.getClass_id()));
				}
			}
		}
        Collections.sort(orders);
        return orders;
    }

	public Collection<BeanOrder> getOrdersByCourse(int id) throws BooksNotFoundException{
		orders= new ArrayList<>();
		String selectStatement = "select order_id,order_time,order_price from `order` where course_id=?";
		this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanOrder>() {
			@Override
			public BeanOrder extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				while(rs.next()) {
					BeanOrder order=new BeanOrder();
					order.setOrder_id(rs.getInt(1));
					order.setOrder_time(rs.getDate(2));
					order.setOrder_price(rs.getFloat(3));
					orders.add(order);
				}
				return null;
			}
		},id);
//		for(BeanOrder order:orders){
//			selectStatement="select student_name,student_age from order_details where student_id=?";
//			this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanStudent>() {
//				@Override
//				public BeanStudent extractData(ResultSet rs) throws SQLException, DataAccessException {
//					// TODO Auto-generated method stub
//					while(rs.next()) {
//						BeanStudent student=new BeanStudent();
//						student.setStudent_id(order.getStudent_id());
//						student.setStudent_name(rs.getString(1));
//						student.setStudent_age(rs.getInt(2));
//						order.setStudent(student);
//					}
//					return null;
//				}
//			},order.getStudent_id());
//		}
		return orders;
	}
//    public Collection<BeanOrder> getAllOrders() throws BooksNotFoundException{
//        orders= new ArrayList<>();
//        String selectStatement = "select order_id,user_id,order_time,order_price,state from orderlist";
//		this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanOrder>() {
//			@Override
//			public BeanOrder extractData(ResultSet rs) throws SQLException, DataAccessException {
//				// TODO Auto-generated method stub
//				while(rs.next()) {
//					BeanOrder order=new BeanOrder();
//					order.setOrder_id(rs.getInt(1));
//					order.setUser_id(rs.getString(2));
//					order.setOrder_time(rs.getDate(3));
//					order.setOrder_price(rs.getFloat(4));
//					order.setState(rs.getBoolean(5));
//					orders.add(order);
//				}
//				return null;
//			}
//		});
//		if(orders.size()>0) {
//			Map<Integer, BeanOrder>mp=new HashMap<>();
//			for(BeanOrder o:orders) {
//				mp.put(o.getOrder_id(), o);
//			}
//			String orderids="";
//			for(int i=0;i<orders.size();i++) {
//				if(i!=0) orderids+=",";
//				orderids+=orders.get(i).getOrder_id();
//			}
//			selectStatement="select order_id,book_id,num,detail_price from order_details where order_id in ("+orderids+") order by order_id desc";
//			Set<Integer>bookids=new HashSet<>();
//			this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanOrderDetail>() {
//				@Override
//				public BeanOrderDetail extractData(ResultSet rs) throws SQLException, DataAccessException {
//					// TODO Auto-generated method stub
//					while(rs.next()) {
//						BeanOrderDetail orderDetails=new BeanOrderDetail();
//						orderDetails.setOrder_id(rs.getInt(1));
//						orderDetails.setBook_id(rs.getString(2));
//						orderDetails.setNum(rs.getInt(3));
//						orderDetails.setDetail_price(rs.getFloat(4));
//						bookids.add(rs.getInt(2));
//						BeanOrder order=mp.get(orderDetails.getOrder_id());
//						if(order!=null) {
//							if(order.getOrderDetails()==null) {
//								order.setOrderDetails(new ArrayList<>());
//							}
//							order.getOrderDetails().add(orderDetails);
//						}
//					}
//					return null;
//				}
//			});
//
//			String strbookids="";
//			for(Integer bookid:bookids) {
//				if(strbookids=="") strbookids=bookid+"";
//				else strbookids+=","+bookid;
//			}
//
//			selectStatement="select book_id,title,author,publisher,publishing_time,book_price,category_id,inventory,description from books where book_id in ("+strbookids+") order by book_id desc";
//
//			Map<String, BeanBook>bookMap=new HashMap<>();
//			this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanBook>() {
//				@Override
//				public BeanBook extractData(ResultSet rs) throws SQLException, DataAccessException {
//					// TODO Auto-generated method stub
//					while(rs.next()) {
//						BeanBook bookDetails=new BeanBook();
//						bookDetails.setBook_id(rs.getString(1));
//						bookDetails.setTitle(rs.getString(2));
//						bookDetails.setAuthor(rs.getString(3));
//						bookDetails.setPublisher(rs.getString(4));
//						bookDetails.setPublishing_time(rs.getInt(5));
//						bookDetails.setBook_price(rs.getFloat(6));
//						bookDetails.setCategory_id(rs.getInt(7));
//						bookDetails.setInventory(rs.getInt(8));
//						bookDetails.setDescription(rs.getString(9));
//						bookMap.put(bookDetails.getBook_id(), bookDetails);
//					}
//					return null;
//				}
//			});
//			for(BeanOrder orderList:orders) {
//				for(BeanOrderDetail d:orderList.getOrderDetails()) {
//					d.setBookDetails(bookMap.get(d.getBook_id()));
//				}
//			}
//		}
//        Collections.sort(orders);
//        return orders;
//    }
    
    public void finishOrder(int orderid) {
    	String sql="update orderlist set state=1 where order_id=?";
    	jdbcTemplate.update(sql, new Object[] {orderid});
    }
    public void deleteOrder(int orderid) {
    	String sql="delete from order_details where order_id=?";
    	jdbcTemplate.update(sql, new Object[] {orderid});
    	sql="delete from orderlist where order_id=?";    	
    	jdbcTemplate.update(sql, new Object[] {orderid});
    }
}
